﻿<?php 
//Config : Les informations personnels de l'instance (log, pass, etc)
require("../../include/config.php");

//API Fonctions : les fonctions fournis de base par l'API
require("../../API/php/fonctions.php");

//Header établie la connection à la base $connection
require("../../API/php/header.php");

//Mode debug
$modeDebug = false;

//Public ou privé (clé obligatoire)
$modePublic = true;

//Mode de sortie text,json,xml,csv
//pour xml et csv $object_retour->data["resultat"] doit contenir qu'un est unique array
$modeSortie = "json";

//Liens de test
// API_RH/phpsql/mysql_getDataRapportRH4.php?milis=123456789&dateBegin=01022014&dateEnd=02032014&filtreUsers=

//[X][0] = type
//[X][1] = description_type
//[X][2] = phase
//[X][3] = description_phase
//[X][4] = group
//[X][5] = nbTotal
//[X][6] = user
//[X][7] = nb

// IN obligatoire
$arrayInput = array(
    "dateBegin" => null,
    "dateEnd" => null
);

//Définition des entrants optionel
$arrayInputOpt = array(
    "filtreUsers" => ""
);

//Récupération des entrants
$arrayValeur = recupInput($arrayInput,$arrayInputOpt);

//Object retour minima
// $object_retour->strErreur string
// $object_retour->data  string
// $object_retour->statut  string

//------------------------------------------------------------------
$object_retour->data = array();

//------------------------------------------------------------------
$strSql = "
    SELECT DISTINCT 
        a.type, 
        a.phase, 
        (select c.description from `".$prefixTable."tab_suivihebdo_type_activite` c where c.id=a.type) as description_type, 
        (select IFNULL(d.description,'') from `".$prefixTable."tab_suivihebdo_type_phase` d where d.id=a.phase) as description_phase, 
        b.ordre, 
        b.group
    FROM (
        SELECT type, phase, date_saisi FROM `".$prefixTable."tab_suivihebdo_enreg` 
        UNION 
        SELECT type, '0' as phase, '0000-00-00' as date_saisi FROM `".$prefixTable."tab_suivihebdo_enreg`
    ) a, tab_suivihebdo_param_ordre_rapport4 b
    WHERE 1=1
    AND a.type = b.type
    AND a.type != 2
    AND ((a.`date_saisi` between STR_TO_DATE('".$arrayValeur["dateBegin"]."','%d%m%Y') AND STR_TO_DATE('".$arrayValeur["dateEnd"]."','%d%m%Y')) or a.`date_saisi` = '0000-00-00')
    ORDER BY b.ordre, a.phase
;";
// On envois la requète
$req = $connection->prepare($strSql);

if($req->execute()){
    while ($row = $req->fetch(PDO::FETCH_ASSOC)) {
        $resultats = new stdClass();
        $resultats->type = $row['type'];
        $resultats->description_type = $row['description_type'];
        $resultats->phase = $row['phase'];
        $resultats->description_phase = $row['description_phase'];
        $resultats->group = $row['group'];

        $cond_phase = "";
        if($row['phase'] == "0"){
            $cond_phase = "";
        }else{
            $cond_phase = "AND c.phase = ".$row['phase'];
        }

        $sql2 = "
            SELECT IFNULL(SUM(charge),0) as nb
            FROM `".$prefixTable."tab_suivihebdo_enreg` c
            WHERE 1=1
            AND c.`date_saisi` between STR_TO_DATE('".$arrayValeur["dateBegin"]."','%d%m%Y') AND STR_TO_DATE('".$arrayValeur["dateEnd"]."','%d%m%Y')
            AND c.type = ".$row['type']."
            ".$cond_phase."
        ;";
        $req2 = $connection->prepare($sql2);
        $rows2 = array();
        if($req2->execute()){
            $rows2 = $req2->fetch();
        }else{
            die('Erreur SQL:'.print_r($req2->errorInfo(), true)." (".$sql2.")");
        }
        $req2->closeCursor();
        $resultats->nbTotal = $rows2['nb'];

        if ($arrayValeur["filtreUsers"] != ""){
            $strFiltreUsers = "AND a.user in (".$arrayValeur["filtreUsers"].")";
        }else{
            $strFiltreUsers = "";
        }

        $sql3 = "
            SELECT DISTINCT UPPER(a.user) user
            FROM `".$prefixTable."tab_suivihebdo_enreg` a
            WHERE 1=1
            ".$strFiltreUsers."
            AND a.`date_saisi` between STR_TO_DATE('".$arrayValeur["dateBegin"]."','%d%m%Y') AND STR_TO_DATE('".$arrayValeur["dateEnd"]."','%d%m%Y')
            ORDER BY a.user
        ;";
        $req3 = $connection->prepare($sql3);

        if($req3->execute()){
            $resultats->users = array();
            while ($row3 = $req3->fetch(PDO::FETCH_ASSOC)) {
                $user = new stdClass();
                $user->user = $row3['user'];
                $user->nb = "0";
                
                if ($arrayValeur["filtreUsers"] != ""){
                    $strFiltreUsers = "AND c.user in (".$arrayValeur["filtreUsers"].")";
                }else{
                    $strFiltreUsers = "";
                }

                $sql4 = "
                    SELECT IFNULL(SUM(charge),0) as nb
                    FROM `".$prefixTable."tab_suivihebdo_enreg` c
                    WHERE 1=1
                    ".$strFiltreUsers."
                    AND c.`date_saisi` between STR_TO_DATE('".$arrayValeur["dateBegin"]."','%d%m%Y') AND STR_TO_DATE('".$arrayValeur["dateEnd"]."','%d%m%Y')
                    AND UPPER(c.user) = '".$row3['user']."'
                    AND c.type = ".$row['type']."
                    ".$cond_phase."
                ;";
                $req4 = $connection->prepare($sql4);
                $rows4 = array();
                if($req4->execute()){
                    $rows4 = $req4->fetch();
                    $user->nb = $rows4['nb'];
                }else{
                    die('Erreur SQL:'.print_r($req4->errorInfo(), true)." (".$sql4.")");
                }
                $req4->closeCursor();
                $resultats->users[] = $user;
            }
        }else{
            die('Erreur SQL:'.print_r($req3->errorInfo(), true)." (".$sql3.")");
        }

        $object_retour->data[] = $resultats;
    }
}else{
    die('Erreur SQL:'.print_r($req->errorInfo(), true)." (".$strSql.")");
}
$req->closeCursor(); 
//--------------------------------------------------------------------------

if($modeDebug){
    $strSorti .= ('<br><br><br><br>'.$sql);
    $strSorti .= ('<br><br><br><br>'.$sql2);
    $strSorti .= ('<br><br><br><br>'.$sql3);
    $strSorti .= ('<br><br><br><br>'.$sql4);
}

require("../../API/php/footer.php");
?>